package com.bear.forum.jdbc.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.bear.PlatFormDao.forum.UserDao;
import com.bear.jdbc.impl.Page;
import com.bear.model.entity.User;
import com.bear.model.entity.balance.MoneyList;
import com.bear.model.entity.balance.MoneyType;

@Repository
public class UserDaoImp implements UserDao{
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int getMatchCount(String username, String password) {
		String sqlStr = "SELECT count(*) from user" +
				" where username=? and password=?";
		return jdbcTemplate.queryForInt(sqlStr,new Object[]{username,password});
				
	}

	@Override
	public User findUserByUserName(final String userName) {
		String sqlStr = "select id,username,credit" +
				" from user where username=?";
		final User user = new User();
		jdbcTemplate.query(sqlStr, new Object[]{userName},
				new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						user.setId(rs.getInt("id"));
						user.setUsername(userName);
						user.setCredit(rs.getInt("credit"));
					}
				});
		return user;
	}

	@Override
	public void updateLoginInfo(User user) {
		String sqlStr = "update user set lastvisit=?,lastip=?," +
				" credit=? where id=?";
		jdbcTemplate.update(sqlStr,new Object[]{user.getLastVist(),
				user.getLastIp(),user.getCredit(),user.getId()});
	}

	@Override
	public User findUserById(final Integer id) {
		String sqlStr = "select id,username,credit,sign,photoAddress" +
				" from user where id=?";
		final User user = new User();
		jdbcTemplate.query(sqlStr, new Object[]{id},
				new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						user.setId(id);
						user.setUsername(rs.getString("username"));
						user.setCredit(rs.getInt("credit"));
						user.setSign(rs.getString("sign"));
						user.setPhotoAddress(rs.getString("photoAddress"));
					}
				});
		return user;
	}

	@Override
	public List<Integer> findFriendsById(Integer id) {
		String sqlStr = "select clientid" +
				" from friends where hostid=?";
		final List<Integer> users = new ArrayList<Integer>();
		jdbcTemplate.query(sqlStr, new Object[]{id},
				new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet rs) throws SQLException {
							users.add(rs.getInt("clientid"));
					}
				});
		return users;
	}

	@Override
	public void updateUsersign(int id,String sign) {
		String sqlStr = "update user set sign=?" +
				" where id=?";
		jdbcTemplate.update(sqlStr,new Object[]{sign,
				id});
		
	}

	@Override
	public void addUser(String username, String password) {
		String sqlStr = "insert into user(username, password)" +
				" values(?,?)";
		jdbcTemplate.update(sqlStr, new Object[]{username,password});
		
	}

	@Override
	public void updateUserHeaderAddress(int id, String headeraddress) {
		String sqlStr = "update user set photoAddress=?" +
				" where id=?";
		jdbcTemplate.update(sqlStr,new Object[]{headeraddress,
				id});
	}

	@Override
	public List<User> pageQuery(int page) {
		int  pageSize=5;
		Assert.isTrue(page>=1,"pageNo should start from 1");
		final List<User> userlists = new ArrayList<User>();
		String countQueryString = "select count(*) from user";
		long totalCount =  jdbcTemplate.queryForLong(countQueryString);
		if(totalCount<1)
			return userlists;
		int startIndex = Page.getStartOfPage(page,pageSize);
		String query = "select id,username,credit,sign,photoAddress from user" +
				" limit "+startIndex+","+pageSize;
		
		jdbcTemplate.query(query,
				new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet rs) throws SQLException {
							User user = new User();
							user.setId(rs.getInt("id"));
							user.setUsername(rs.getString("username"));
							user.setCredit(rs.getInt("credit"));
							user.setSign(rs.getString("sign"));
							user.setPhotoAddress(rs.getString("photoAddress"));
							userlists.add(user);
					}
				});
		return userlists;
	}

	@Override
	public long getCount() {
		String sqlStr = "SELECT count(*) from user";
		return jdbcTemplate.queryForInt(sqlStr);
	}

	@Override
	public int findRelation(Integer to, int from) {
		String sqlStr = "SELECT count(*) from friends where hostid=? and clientid=?";
		return jdbcTemplate.queryForInt(sqlStr,new Object[]{to,from});
		
	}

	@Override
	public void addrelation(int usera, Integer userb) {
		String sql = "insert into friends(hostid,clientid) values(?,?)";
		jdbcTemplate.update(sql, new Object[]{usera,userb});
		jdbcTemplate.update(sql, new Object[]{userb,usera});
	}

}
